from DataAccess.DBConnFactory import DBConnFactory
from DataAccess.blp_client import query_blp_data
from Misc.Utils import *
from Misc.Constant import *

from datetime import date
from string import Template


def update_stock_daily_close(ref_date):
	pass	


def update_local_db_from_vendor(ref_date, tick_list, dest_db_conn, mkt=None):
	fc_conn = DBConnFactory().get_db_connection('FINCHINA')	
	fc_cursor = fc_conn.cursor()
	
	tick_criteria = '('
	first_item = True
	for item in tick_list:
		if not first_item:
			tick_criteria += ','
		tick_criteria += "'"+item+"'"
		first_item = False
	tick_criteria += ')'
	
	tbl = 'chdquote'
	exch = '''(exchange='CNSESH' or exchange='CNSESZ')'''
	if mkt and mkt=='HK':
		tbl = 'HKHDQUOTE'
		exch = '''exchange='HKSE00' '''
	
	sql_tpl = Template('''select TO_CHAR(TO_DATE(tdate,'yyyymmdd'),'yyyy-mm-dd'), 
					symbol,tclose 
					from ${TBL}
					where symbol in ${TK_CR}
						and ${EXCH_CR}
						and TO_DATE(tdate,'yyyymmdd')=TO_DATE('${DATE}','yyyy-mm-dd')
						and tclose>0.00''')
	sql_text = sql_tpl.substitute(TBL=tbl, TK_CR=tick_criteria, EXCH_CR=exch,  
								DATE=ref_date.isoformat())
	#print 'debug', sql_text
	fc_cursor.execute(sql_text)
	r = fc_cursor.fetchall()
	
	if len(r)>0:
		dest_db_conn.cursor().executemany('''merge into comm_security_daily_price p
								using (select :1 d,:2 tk,:3 v from dual) t
									on (p.ref_date=TO_DATE(t.d,'yyyy-mm-dd') and t.tk=p.ticker)
								when matched then update 
									set p.close_price=t.v
								when not matched then insert 
									values(TO_DATE(t.d,'yyyy-mm-dd'),t.tk,t.v)''', r)
		dest_db_conn.commit()
		

def update_local_db_from_blp(ref_date, tick_list, tick_format_func, dest_db_conn):
	if len(tick_list)==0:
		return

	print 'blp_tick:', tick_list
		
	fmt_d = ref_date.isoformat()
	if ref_date==date.today():
		rsp = query_blp_data(tick_list, ['PX_LAST'])
		print rsp
		r = [(fmt_d, tick_format_func(k), v['PX_LAST']) for (k,v) in rsp.items()]
	else:
		rsp = query_blp_data(tick_list, ['PX_LAST'], ref_date, ref_date)
		print rsp
		r = [(fmt_d, tick_format_func(k), v[fmt_d]['PX_LAST']) for (k,v) in rsp.items() if v.has_key(fmt_d)]
	
	if len(r)>0:
		dest_db_conn.cursor().executemany('''merge into comm_security_daily_price p
								using (select :1 d,:2 tk,:3 v from dual) t
									on (p.ref_date=TO_DATE(t.d,'yyyy-mm-dd') and t.tk=p.ticker)
								when matched then update 
									set p.close_price=t.v
								when not matched then insert 
									values(TO_DATE(t.d,'yyyy-mm-dd'),t.tk,t.v)''', r)
		dest_db_conn.commit()
	
	
def update_CH_stock_daily_price(ref_date):
	#market is null, A or B shares
	sql_tpl = Template('''
				select distinct ticker 
				from daily_position 
				where ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
					and security_type='STOCK' 
					and (market is null or market='')
			union 
				select distinct underlying_ticker 
				from daily_position dp
				join pnote_info pn 
					on dp.ticker=pn.isin_code 
				where dp.ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
					and security_type='PNOTE' ''')
	
	conn = DBConnFactory().get_db_connection('PKEDB')
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()
	cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	tmp = cursor.fetchall()	
	if len(tmp)==0:
		return 
	
	if ref_date==date.today():  #get the price from BLP	
		bb_tick_list = [ch_ticker_to_blp_ticker(entry[0]) for entry in tmp]
		#print 'CH_stock_list, ', bb_tick_list
		update_local_db_from_blp(ref_date, bb_tick_list, blp_ticker_to_ch_ticker, conn)
	else: #hist data from vendar
		tick_list = [entry[0] for entry in tmp]
		update_local_db_from_vendor(ref_date, tick_list, conn)
		
	
def update_HK_stock_daily_price(ref_date):
	sql_tpl = Template('''select distinct ticker 
				from daily_position 
				where ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
					and security_type='STOCK' 
					and market='HK' ''')
	
	conn = DBConnFactory().get_db_connection('PKEDB')
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()
	cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	tmp = cursor.fetchall()
	if len(tmp)==0:
		return 
	
	if ref_date==date.today():  #get the price from BLP		
		bb_tick_list = [hk_ticker_to_blp_ticker(entry[0]) for entry in tmp]
		#print 'HK_stock_list, ', bb_tick_list	
		update_local_db_from_blp(ref_date, bb_tick_list, blp_ticker_to_hk_ticker, conn)
	else: #hist data from vendar
		tick_list = [entry[0] for entry in tmp]
		update_local_db_from_vendor(ref_date, tick_list, conn, 'HK')
		
	
def update_US_stock_daily_price(ref_date):
	sql_tpl = Template('''select distinct ticker 
				from daily_position 
				where ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
					and security_type='STOCK' 
					and market in (${US_EQ_MKT}) ''')
	
	conn = DBConnFactory().get_db_connection('PKEDB')
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()
	cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat(), 
										US_EQ_MKT=US_EQUITY_MKT))
	tmp = cursor.fetchall()
	if len(tmp)==0:
		return 
		
	bb_tick_list = [entry[0] for entry in tmp]
	update_local_db_from_blp(ref_date, bb_tick_list, lambda x:x, conn)
	

def update_futures_daily_settlement_price(ref_date):
	sql_tpl = Template('''select distinct ticker 
				from daily_position 
				where ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
					and security_type='FUTURES' ''')
	
	conn = DBConnFactory().get_db_connection('PKEDB')
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()
	cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	tmp = cursor.fetchall()
	if len(tmp)==0:
		return 
	
	bb_tick_list = [entry[0] for entry in tmp]
	update_local_db_from_blp(ref_date, bb_tick_list, lambda x:x, conn)
	
	
def update_all_daily_price(start_date, end_date):
	#start_date = date(2012, 8, 31)
	#end_date = date(2012, 12, 1)
	one_day = timedelta(days=1)
	
	trade_date = start_date		
	while trade_date<end_date:
	
		update_CH_stock_daily_close(trade_date)
		#update_HK_stock_daily_close(trade_date)
		#update_US_stock_daily_close(trade_date)
		#update_futures_daily_settlement_price(trade_date)
		
		print 'info, ', trade_date.isoformat(), ' position complete.'		
		trade_date += one_day
	
	return	
	
	